package com.zdkj.subway.stm.reportquery.util;

import com.alibaba.fastjson.JSONObject;
import com.zdkj.subway.stm.util.JdbcUtil;

import org.apache.commons.lang3.StringUtils;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.*;

/**
 *  售票员结算单日报表查询接口
 */
public class ConductorSettleUtil {
	
	/**
	 * 钱箱的钱
	 * select sum(ifnull(check_amount,0)) as realCount from sw_st_tvm_check_detail where line_code='3' and station_code='41' and revenue_time='2020-12-10' and del_flag='0'
	 补币金额
	 select  sum(ifnull(total_supplement_amount,0)) from  sw_st_tvm_supplement_currency  where  line_code='3' and station_code='41' and del_flag='0' and supplement_current_time between('前一日01:59')
	 
	 * 补短款
	 * select convert(sum(ifnull(short_amount,0))/100,decimal(16,2)) from sw_au_conductor_short  where line_code='3' and station_code='41' and DATE_FORMAT(SUP_PAYMENT_TIME,'%Y-%m-%d') = '2020-12-10'
	 * @param param
	 * @return
	 * @throws SQLException
	 */
	
	public List getTicketSellDetail(Map<String,String> param) throws SQLException{
		
		  String lineCode=param.get("lineCode");
	      String stationCode=param.get("stationCode");
	      String statisticsDate=param.get("statisticsDate");
	      String classNumber = param.get("classNumber");
	      String conductorJobNo=param.get("conductorJobNo");
	      String bomCode=param.get("bomCode");
	      List<String> ticketNameScope = new ArrayList<String>();
	      ticketNameScope.add("YXCP");
	      ticketNameScope.add("DAYCP");
	      ticketNameScope.add("YZDCP");
	      ticketNameScope.add("YJZP");
	      ticketNameScope.add("DCP");
	      ticketNameScope.add("OTHER");
	      //根据各种票种统计售卖情况
	      for(int i=0;i<ticketNameScope.size();i++){
	    	  
	    	  String ticketName = ticketNameScope.get(i);
	    	  if(ticketName.equals("DAYCP") || ticketNameScope.equals("OTHER")){
	    		  
	    	  }else{
	    		  Map mapTicketSell = new HashMap();
	    		  StringBuilder sb = new StringBuilder();
	    		  sb.append("select sd.ticket_name,sd.ticket_type,sd.ticket_value,sd.windows_num,sd.windows_close_num,sd.invalid_ticket_num ")
	    		  .append(" from sw_st_ticket_petty_add st,sw_st_ticket_petty_add_details sd  ")
	    		  .append(" where st.ID = sd.RELATION_ID ")
	    		  .append(" and st.line_code='"+lineCode+"' and st.station_code='"+stationCode+"' and st.ns_job_no='"+conductorJobNo+"'  ")
	    		  .append(" and st.ns_shift='"+classNumber+"'  ")
	    		  .append(" and sd.TICKET_NAME='"+ticketName+"' ")
	    		  .append(" and sd.type='2' and st.confirm_status='1' ")
	    		  .append(" and sd.del_flag='0' ")
	    		  .append(" and st.del_flag='0'");
	    		  
	    		   List<Map<String, Object>> maps = JdbcUtil.exeSqlQuery(sb.toString());
	    		    if(null != maps && 0 != maps.size()){
	    		        int windowsAll = 0;
	    		        int windowsCloseAll = 0;
	    		        for(Map m : maps){
	    		            String type = String.valueOf(m.get("TYPE"));
	    		            if(("0".equals(type) || "1".equals(type)) && null != m.get("WINDOWS_NUM")){
	    		               
	    		                Integer windows_num = Integer.valueOf(String.valueOf(m.get("WINDOWS_NUM")));
	    		                windowsAll += windows_num;
	    		            }else if("2".equals(type) && null != m.get("WINDOWS_CLOSE_NUM")){
	    		                
	    		                Integer windows_close_num = Integer.valueOf(String.valueOf(m.get("WINDOWS_CLOSE_NUM")));
	    		                windowsCloseAll += windows_close_num;
	    		            }
	    		        }
	    		        //swStStock.setBoxOfficeSale(windowsAll-windowsCloseAll);
	    		    }
	    	  }
	    	  
	      }
	      /*
	       * select sd.ticket_name,sd.ticket_type,sd.ticket_value,sd.windows_num,sd.windows_close_num,sd.invalid_ticket_num
from sw_st_ticket_petty_add st,sw_st_ticket_petty_add_details sd 
where st.ID = sd.RELATION_ID
and st.line_code='1' and st.station_code='17' and st.ns_job_no='Q010667' 
and st.ns_shift='18:00:00-20:00:00' 
and sd.TICKET_NAME='YXCP'
and sd.type='2'
and sd.del_flag='0'
and st.del_flag='0'
	       */
	      String sqlTicketType = "select distinct tci";
	      
		return null;

	}

	
   

    /**
     * 票款差异审核主表-补币金额
     * @return
     */
    public static BigDecimal getSupplementAmount(Map<String,Object> param,String cashBoxType,String tvm_cash_box_num) throws SQLException, ParseException {
        Object lineCode=param.get("lineCode");
        Object stationCode=param.get("stationCode");
        Object tvmCode=param.get("tvmCode");
        Object startSettleDate = param.get("js_startSettleDate");
        Object endSettleDate = param.get("js_endSettleDate");
        if(startSettleDate==null){
            startSettleDate=param.get("settleStartTime");
        }
        if(endSettleDate==null){
            endSettleDate=param.get("settleEndTime");
        }
        String sql="SELECT ifnull(sum(sscd.SUPPLEMENT_AMOUNT),0) supplement_amount from sw_st_tvm_supplement_currency_details sscd left join sw_st_tvm_supplement_currency ssc\n" +
                "on sscd.RELATION_ID = ssc.id\n" +
                "where sscd.TVM_CODE='"+tvmCode+"' " +
                "and sscd.TVM_CASH_BOX_NUM = '"+tvm_cash_box_num+"'"+
                "and ssc.LINE_CODE='"+lineCode+"' " +
                "and ssc.STATION_CODE='"+stationCode+"' " +
                "and ssc.SUPPLEMENT_CURRENCY_TIME between '"+startSettleDate+"' and '"+endSettleDate+"' " +
                "and sscd.tvm_cash_box_type='"+cashBoxType+"'";

        List<Map<String, Object>> maps = JdbcUtil.exeSqlQuery(sql);
        BigDecimal supplement_amount =(BigDecimal) maps.get(0).get("supplement_amount");
        return supplement_amount;
    }

    
}
